package cn.stylefeng.guns.sys.modular.system.service;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.sys.core.util.ExcelUtil;
import cn.stylefeng.guns.sys.core.util.UUIDUtils;
import cn.stylefeng.guns.sys.modular.system.entity.*;
import cn.stylefeng.guns.sys.modular.system.mapper.BtyyCwCkpxMapper;
import cn.stylefeng.guns.sys.modular.system.model.params.CwbbParam;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class BtyCkpxService extends ServiceImpl<BtyyCwCkpxMapper, BtyyCwCkpx> {

    @Resource
    private BtyyCwCkpxMapper ckpxMapper;

    @Resource
    private BtyKmmxzService kmmxzService;
    @Resource
    private BtyFzhsService fzhsService;
    @Resource
    private BtyMdpdService mdpdService;
    @Resource
    private BtyYhlsService yhlsService;
    @Resource
    private BtyPzxqService pzxqService;
    @Resource
    private BtySyfsService syfsService;
    @Resource
    private HuopeiService huopeiService;
    @Resource
    private BtyCWGainLossService btyCWGainLossService;

   /**
     * 获取仓库品项列表
     */
    public Page<Map<String, Object>> list(CwbbParam cwbbParam) {
        Page page = LayuiPageFactory.defaultPage();
        return this.baseMapper.list(page,cwbbParam);
    }
    /**
     * 获取仓库品项列表（不分页）
     */
    public List<BtyyCwCkpx> listExportCkpx(CwbbParam cwbbParam) {

        return this.baseMapper.listExportCkpx(cwbbParam);
    }
    /**
     * 批量导出财务报表信息
     */
    @Transactional(rollbackFor = Exception.class)
    public void expExcel(HttpServletResponse response, CwbbParam cwbbParam){
        //设置日期格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String number = sdf.format(new Date());
        DecimalFormat format = new DecimalFormat("0.00");
        // 查询数据
        List<BtyyCwKmmxz> listFymx;
        List<BtyyCwFzhsye>listFzhs;
        List<BtyyCwKmmxz>listXjrjz;
        List<BtyyCwKmmxz>listYhyye;
        List<BtyyCwSyfs>listSyfs;
        List<BtyyCwMdpd>listMdpd;
        List<BtyyCwCkpx> listCkpx;
        List<BtyyCwYhls>listYhls;
        List<BtyyCwPzxq>listPzxq;
        List<Huopei> listHuopei;
        List<BtyCWGainLoss> listSunYi;
        List<BtyCWGainLoss> listZiChan;
        String fileName = number+"上交报表导出";
        String path ="D:\\"+"xls\\";
        File f = new File(path);
        if(!f.exists()){
            f.mkdirs(); //创建目录
        }
        String url = path + fileName + ".xls";
        System.out.println("url:"+url);
        try {
            //损益列表查询
            BtyCWGainLoss sunYi = new BtyCWGainLoss();
            sunYi.setType("1");
            sunYi.setDate(cwbbParam.getTimeLimit());
            sunYi.setDm(cwbbParam.getDm());
            listSunYi = btyCWGainLossService.listAll(sunYi);
            //资产负债列表查询
            BtyCWGainLoss ziChan = new BtyCWGainLoss();
            ziChan.setType("2");
            ziChan.setDate(cwbbParam.getTimeLimit());
            ziChan.setDateYest((Integer.parseInt(cwbbParam.getTimeLimit())-1)+"");
            ziChan.setDm(cwbbParam.getDm());
            listZiChan = btyCWGainLossService.listAll(ziChan);
            //费用明细列表查询
            listFymx =kmmxzService.listExportFymx(cwbbParam);
            //辅助核算余额列表查询
            listFzhs=fzhsService.listExportFzhs(cwbbParam);
            //现金日记账列表查询
            listXjrjz=kmmxzService.listExportXjrjz(cwbbParam);
            //银行营业额列表查询
            listYhyye=kmmxzService.listExportYhyye(cwbbParam);
            //应收明细列表查询
            listSyfs=syfsService.listExportSyfs(cwbbParam);
            //门店盘点列表查询
            listMdpd=mdpdService.listExportMdpd(cwbbParam);
            //(收发存)仓库品项列表查询
            listCkpx = ckpxMapper.listExportCkpx(cwbbParam);
            //银行流水列表查询
            listYhls=yhlsService.listExportYhls(cwbbParam);
            //凭证详情列表
            listPzxq=pzxqService.listExportPzxq(cwbbParam);

            Huopei huopei = new Huopei();
            if (ToolUtil.isNotEmpty(cwbbParam.getDm())){
                huopei.setShopName(cwbbParam.getDm());
            }

//            if (cwbbParam.getBeginTime() != null){
//                huopei.setBeginTime(sdf.parse(cwbbParam.getBeginTime()));
//            }
//            if (cwbbParam.getEndTime() != null){
//                huopei.setEndTime(sdf.parse(cwbbParam.getEndTime()));
//            }

            listHuopei = huopeiService.exportHuopei(huopei,cwbbParam.getTimeLimit());

            //费用明细数据
            List<String[]> dataset = new ArrayList<>();
            for (int i = 0; i < listFymx.size(); i++) {
                String[] arr = new String[12];
                arr[0] = i+1+"";
                arr[1] = listFymx.get(i).getDm()==null ? "" : listFymx.get(i).getDm();
                arr[2] = listFymx.get(i).getRq()==null ? "" : listFymx.get(i).getRq();
                arr[3] = listFymx.get(i).getKmbm()==null ? "" : listFymx.get(i).getKmbm();
                arr[4] = listFymx.get(i).getKmmc()==null ? "" : listFymx.get(i).getKmmc();
                arr[5] = listFymx.get(i).getPzzh()==null ? "" : listFymx.get(i).getPzzh();
                arr[6] = listFymx.get(i).getZy()==null ? "" : listFymx.get(i).getZy();
                arr[7] = listFymx.get(i).getDfkm()==null ? "" : listFymx.get(i).getDfkm();
                String jf=getString(format,listFymx.get(i).getJf());
                arr[8] = jf;
                String df=getString(format,listFymx.get(i).getDf());
                arr[9] = df;
                arr[10] = listFymx.get(i).getFx()==null ? "" : listFymx.get(i).getFx();
                String ye=getString(format,listFymx.get(i).getYe());
                arr[11] = ye;
                dataset.add(arr);
            }

            //辅助核算余额数据
            List<String[]> datasetFzhs = new ArrayList<>();
            for (int i = 0; i < listFzhs.size(); i++) {
                String[] arr2 = new String[14];
                arr2[0] = i+1+"";
                arr2[1] = listFzhs.get(i).getDm()==null ? "" : listFzhs.get(i).getDm();
                arr2[2] = listFzhs.get(i).getKmlb()==null ? "" : listFzhs.get(i).getKmlb();
                arr2[3] = listFzhs.get(i).getKmbm()==null ? "" : listFzhs.get(i).getKmbm();
                arr2[4] = listFzhs.get(i).getKmmc()==null ? "" : listFzhs.get(i).getKmmc();
                arr2[5] = listFzhs.get(i).getFzhs()==null ? "" : listFzhs.get(i).getFzhs();
                arr2[6] = listFzhs.get(i).getQcfx()==null ? "" : listFzhs.get(i).getQcfx();
                String qcye = getString(format, listFzhs.get(i).getQcye());
                arr2[7] = qcye;
                String bqjf = getString(format, listFzhs.get(i).getBqjf());
                arr2[8] =bqjf;
                String bqdf = getString(format, listFzhs.get(i).getBqdf());
                arr2[9] = bqdf;
                String jflj = getString(format, listFzhs.get(i).getJflj());
                arr2[10] =jflj;
                String dflj = getString(format, listFzhs.get(i).getDflj());
                arr2[11] = dflj;
                arr2[12] = listFzhs.get(i).getQmfx()==null ? "" : listFzhs.get(i).getQmfx();
                String qmye=getString(format, listFzhs.get(i).getQmye());
                arr2[13] = qmye;
                datasetFzhs.add(arr2);
            }
            //现金日记账数据
            List<String[]>datasetXjrjz=new ArrayList<>();
            for (int i = 0; i < listXjrjz.size(); i++) {
                String[] arr3 = new String[12];
                arr3[0] = i+1+"";
                arr3[1] = listXjrjz.get(i).getDm()==null ? "" : listXjrjz.get(i).getDm();
                arr3[2] = listXjrjz.get(i).getRq()==null ? "" : listXjrjz.get(i).getRq();
                arr3[3] = listXjrjz.get(i).getKmbm()==null ? "" : listXjrjz.get(i).getKmbm();
                arr3[4] = listXjrjz.get(i).getKmmc()==null ? "" : listXjrjz.get(i).getKmmc();
                arr3[5] = listXjrjz.get(i).getPzzh()==null ? "" : listXjrjz.get(i).getPzzh();
                arr3[6] = listXjrjz.get(i).getZy()==null ? "" : listXjrjz.get(i).getZy();
                arr3[7] = listXjrjz.get(i).getDfkm()==null ? "" : listXjrjz.get(i).getDfkm();
                String jf=getString(format,listXjrjz.get(i).getJf());
                arr3[8] = jf;
                String df=getString(format,listXjrjz.get(i).getDf());
                arr3[9] = df;
                arr3[10] = listXjrjz.get(i).getFx()==null ? "" : listXjrjz.get(i).getFx();
                String ye=getString(format,listXjrjz.get(i).getYe());
                arr3[11] = ye;
                datasetXjrjz.add(arr3);
            }

            //银行营业额数据
            List<String[]>datasetYhyye=new ArrayList<>();
            for (int i = 0; i < listYhyye.size(); i++) {
                String[] arr3 = new String[12];
                arr3[0] = i+1+"";
                arr3[1] = listYhyye.get(i).getDm()==null ? "" : listYhyye.get(i).getDm();
                arr3[2] = listYhyye.get(i).getRq()==null ? "" : listYhyye.get(i).getRq();
                arr3[3] = listYhyye.get(i).getKmbm()==null ? "" : listYhyye.get(i).getKmbm();
                arr3[4] = listYhyye.get(i).getKmmc()==null ? "" : listYhyye.get(i).getKmmc();
                arr3[5] = listYhyye.get(i).getPzzh()==null ? "" : listYhyye.get(i).getPzzh();
                arr3[6] = listYhyye.get(i).getZy()==null ? "" : listYhyye.get(i).getZy();
                arr3[7] = listYhyye.get(i).getDfkm()==null ? "" : listYhyye.get(i).getDfkm();
                String jf=getString(format,listYhyye.get(i).getJf());
                arr3[8] = jf;
                String df=getString(format,listYhyye.get(i).getDf());
                arr3[9] = df;
                arr3[10] = listYhyye.get(i).getFx()==null ? "" : listYhyye.get(i).getFx();
                String ye=getString(format,listYhyye.get(i).getYe());
                arr3[11] = ye;
                datasetYhyye.add(arr3);
            }
            //应收明细(收银方式)数据
            List<String[]>datasetYhmx=new ArrayList<>();
            for (int i = 0; i < listSyfs.size(); i++) {
                String[] arr3 = new String[14];
                arr3[0] = i+1+"";
                arr3[1] = listSyfs.get(i).getDm()==null ? "" : listSyfs.get(i).getDm();
                arr3[2] = listSyfs.get(i).getRq()==null ? "" : listSyfs.get(i).getRq();
                arr3[3] = listSyfs.get(i).getSyfsbm()==null ? "" : listSyfs.get(i).getSyfsbm();
                arr3[4] = listSyfs.get(i).getSyfsmc()==null ? "" : listSyfs.get(i).getSyfsmc();
                arr3[5] = listSyfs.get(i).getKmbm()==null ? "" : listSyfs.get(i).getKmbm();
                arr3[6] = listSyfs.get(i).getKmmc()==null ? "" : listSyfs.get(i).getKmmc();
                arr3[7] = listSyfs.get(i).getPzzh()==null ? "" : listSyfs.get(i).getPzzh();
                arr3[8] = listSyfs.get(i).getZy()==null ? "" : listSyfs.get(i).getZy();
                arr3[9] = listSyfs.get(i).getDfkm()==null ? "" : listSyfs.get(i).getDfkm();
                //String jf=getString(format,listSyfs.get(i).getJf());
                arr3[10] = listSyfs.get(i).getJf()==null ? "" : listSyfs.get(i).getJf();
                //String df=getString(format,listSyfs.get(i).getDf());
                arr3[11] =listSyfs.get(i).getDf()==null ? "" : listSyfs.get(i).getDf();
                arr3[12] = listSyfs.get(i).getFx()==null ? "" : listSyfs.get(i).getFx();
                //String ye=getString(format,listSyfs.get(i).getYe());
                arr3[13] = listSyfs.get(i).getYe()==null ? "" : listSyfs.get(i).getYe();
                datasetYhmx.add(arr3);
            }
            //门店盘点数据
            List<String[]>datasetMdpd=new ArrayList<>();
            for (int i = 0; i < listMdpd.size(); i++) {
                String[] arr = new String[29];
                arr[0]=listMdpd.get(i).getDm()==null ? "" : listMdpd.get(i).getDm();
                arr[1]=listMdpd.get(i).getPxlb()==null ? "" : listMdpd.get(i).getPxlb();
                arr[2]=listMdpd.get(i).getPxbh()==null ? "" : listMdpd.get(i).getPxbh();
                arr[3]=listMdpd.get(i).getPxmc()==null ? "" : listMdpd.get(i).getPxmc();
                arr[4]=listMdpd.get(i).getBzxx()==null ? "" : listMdpd.get(i).getBzxx();
                arr[5]=listMdpd.get(i).getPxgg()==null ? "" : listMdpd.get(i).getPxgg();
                arr[6]=listMdpd.get(i).getZcsl()==null ? "" : listMdpd.get(i).getZcsl();
                arr[7]=listMdpd.get(i).getDw()==null ? "" : listMdpd.get(i).getDw();
                arr[8]=listMdpd.get(i).getZcslTwo()==null ? "" : listMdpd.get(i).getZcslTwo();
                arr[9]=listMdpd.get(i).getDwTwo()==null ? "" : listMdpd.get(i).getDwTwo();
                arr[10]=listMdpd.get(i).getSpsl()==null ? "" : listMdpd.get(i).getSpsl();
                arr[11]=listMdpd.get(i).getDwThree()==null ? "" : listMdpd.get(i).getDwThree();
                arr[12]=listMdpd.get(i).getSpslTwo()==null ? "" : listMdpd.get(i).getSpslTwo();
                arr[13]=listMdpd.get(i).getDwFour()==null ? "" : listMdpd.get(i).getDwFour();
                String zcje=getString(format,listMdpd.get(i).getZcje());
                arr[14]=zcje;
                String hszcje=getString(format,listMdpd.get(i).getHszcje());
                arr[15]=hszcje;
                String spje=getString(format,listMdpd.get(i).getSpje());
                arr[16]=spje;
                String hsspje=getString(format, listMdpd.get(i).getHsspje());
                arr[17]=hsspje;
                arr[18]=listMdpd.get(i).getCysj()==null ? "" : listMdpd.get(i).getCysj();
                arr[19]=listMdpd.get(i).getDwFive()==null ? "" : listMdpd.get(i).getDwFive();
                String ykje=getString(format,listMdpd.get(i).getYkje());
                arr[20]=ykje;
                String hspkje=getString(format,listMdpd.get(i).getHspkje());
                arr[21]=hspkje;
                arr[22]=listMdpd.get(i).getFzdw()==null ? "" : listMdpd.get(i).getFzdw();
                arr[23]=listMdpd.get(i).getFzdwzcsl()==null ? "" : listMdpd.get(i).getFzdwzcsl();
                arr[24]=listMdpd.get(i).getFzdwspsl()==null ? "" : listMdpd.get(i).getFzdwspsl();
                arr[25]=listMdpd.get(i).getFzdwcysl()==null ? "" : listMdpd.get(i).getFzdwcysl();
                arr[26]=listMdpd.get(i).getKwh()==null ? "" : listMdpd.get(i).getKwh();
                arr[27]=listMdpd.get(i).getPch()==null ? "" : listMdpd.get(i).getPch();
                arr[28]=listMdpd.get(i).getBz()==null ? "" : listMdpd.get(i).getBz();
                datasetMdpd.add(arr);
            }
            //获取仓库品项数据
            List<String[]>datasetCkpx=new ArrayList<>();
            for (int i = 0; i < listCkpx.size(); i++) {
                String[] arr = new String[25];
                arr[0]=i+1+"";
                arr[1]=listCkpx.get(i).getMdbh()==null ? "" : listCkpx.get(i).getMdbh();
                arr[2]=listCkpx.get(i).getMdmc()==null ? "" : listCkpx.get(i).getMdmc();
                arr[3]=listCkpx.get(i).getPxlbmc()==null ? "" : listCkpx.get(i).getPxlbmc();
                arr[4]=listCkpx.get(i).getPxbh()==null ? "" : listCkpx.get(i).getPxbh();
                arr[5]=listCkpx.get(i).getPxmc()==null ? "" : listCkpx.get(i).getPxmc();
                arr[6]=listCkpx.get(i).getPxgg()==null ? "" : listCkpx.get(i).getPxgg();
                arr[7]=listCkpx.get(i).getDw()==null ? "" : listCkpx.get(i).getDw();
                arr[8]=listCkpx.get(i).getFsdj()==null ? "" : listCkpx.get(i).getFsdj();
                arr[9]=listCkpx.get(i).getQcsl()==null ? "" : listCkpx.get(i).getQcsl();
                arr[10]=listCkpx.get(i).getQcje()==null ? "" : listCkpx.get(i).getQcje();
                arr[11]=listCkpx.get(i).getLjrksl()==null ? "" : listCkpx.get(i).getLjrksl();
                arr[12]=listCkpx.get(i).getLjrkje()==null ? "" : listCkpx.get(i).getLjrkje();
                arr[13]=listCkpx.get(i).getLjdrsl()==null ? "" : listCkpx.get(i).getLjdrsl();
                arr[14]=listCkpx.get(i).getLjdrje()==null ? "" : listCkpx.get(i).getLjdrje();
                arr[15]=listCkpx.get(i).getLjcksl()==null ? "" : listCkpx.get(i).getLjcksl();
                arr[16]=listCkpx.get(i).getLjckje()==null ? "" : listCkpx.get(i).getLjckje();
                arr[17]=listCkpx.get(i).getLjdcsl()==null ? "" : listCkpx.get(i).getLjdcsl();
                arr[18]=listCkpx.get(i).getLjdcje()==null ? "" : listCkpx.get(i).getLjdcje();
                arr[19]=listCkpx.get(i).getLjpysl()==null ? "" : listCkpx.get(i).getLjpysl();
                arr[20]=listCkpx.get(i).getLjpyje()==null ? "" : listCkpx.get(i).getLjpyje();
                arr[21]=listCkpx.get(i).getLjpksl()==null ? "" : listCkpx.get(i).getLjpksl();
                arr[22]=listCkpx.get(i).getLjpkje()==null ? "" : listCkpx.get(i).getLjpkje();
                arr[23]=listCkpx.get(i).getBqjcsl()==null ? "" : listCkpx.get(i).getBqjcsl();
                arr[24]=listCkpx.get(i).getBqjcje()==null ? "" : listCkpx.get(i).getBqjcje();
                datasetCkpx.add(arr);
            }
            //获取银行流水数据
            List<String[]>datasetYhls=new ArrayList<>();
            for (int i = 0; i < listYhls.size(); i++) {
                String[] arr = new String[9];
                arr[0]=listYhls.get(i).getDm()==null ? "" : listYhls.get(i).getDm();
                arr[1]=listYhls.get(i).getRq()==null ? "" : listYhls.get(i).getRq();
                String zc=getString(format,listYhls.get(i).getZc());
                arr[2]=zc;
                String sr=getString(format,listYhls.get(i).getSr());
                arr[3]=sr;
                String ye=getString(format,listYhls.get(i).getYe());
                arr[4]=ye;
                arr[5]=listYhls.get(i).getZy()==null ? "" : listYhls.get(i).getZy();
                arr[6]=listYhls.get(i).getHm()==null ? "" : listYhls.get(i).getHm();
                arr[7]=listYhls.get(i).getSj()==null ? "" : listYhls.get(i).getSj();
                arr[8]=listYhls.get(i).getBh()==null ? "" : listYhls.get(i).getBh();
                datasetYhls.add(arr);
            }
            //获取凭证详情数据
            List<String[]>datasetPzxq=new ArrayList<>();
            for (int i = 0; i < listPzxq.size(); i++) {
                String[] arr = new String[34];
                arr[0]=listPzxq.get(i).getDm()==null ? "" : listPzxq.get(i).getDm();
                arr[1]=listPzxq.get(i).getZdrq()==null ? "" : listPzxq.get(i).getZdrq();
                arr[2]=listPzxq.get(i).getPzlb()==null ? "" : listPzxq.get(i).getPzlb();
                arr[3]=listPzxq.get(i).getPzbh()==null ? "" : listPzxq.get(i).getPzbh();
                arr[4]=listPzxq.get(i).getFjzs()==null ? "" : listPzxq.get(i).getFjzs();
                arr[5]=listPzxq.get(i).getCjr()==null ? "" : listPzxq.get(i).getCjr();
                arr[6]=listPzxq.get(i).getXh()==null ? "" : listPzxq.get(i).getXh();
                arr[7]=listPzxq.get(i).getKmbh()==null ? "" : listPzxq.get(i).getKmbh();
                arr[8]=listPzxq.get(i).getKmmc()==null ? "" : listPzxq.get(i).getKmmc();
                arr[9]=listPzxq.get(i).getZy()==null ? "" : listPzxq.get(i).getZy();
                arr[10]=listPzxq.get(i).getJfje()==null ? "" : listPzxq.get(i).getJfje();
                arr[11]=listPzxq.get(i).getDfje()==null ? "" : listPzxq.get(i).getDfje();
                arr[12]=listPzxq.get(i).getFzhs()==null ? "" : listPzxq.get(i).getFzhs();
                arr[13]=listPzxq.get(i).getFzhsmc()==null ? "" : listPzxq.get(i).getFzhsmc();
                arr[14]=listPzxq.get(i).getFzhsTwo()==null ? "" : listPzxq.get(i).getFzhsTwo();
                arr[15]=listPzxq.get(i).getFzhsmcTwo()==null ? "" : listPzxq.get(i).getFzhsmcTwo();
                arr[16]=listPzxq.get(i).getFzhsThree()==null ? "" : listPzxq.get(i).getFzhsThree();
                arr[17]=listPzxq.get(i).getFzhsmcThree()==null ? "" : listPzxq.get(i).getFzhsmcThree();
                arr[18]=listPzxq.get(i).getFzhsFour()==null ? "" : listPzxq.get(i).getFzhsFour();
                arr[19]=listPzxq.get(i).getFzhsmcFour()==null ? "" : listPzxq.get(i).getFzhsmcFour();
                arr[20]=listPzxq.get(i).getFzhsFive()==null ? "" : listPzxq.get(i).getFzhsFive();
                arr[21]=listPzxq.get(i).getFzhsmcFive()==null ? "" : listPzxq.get(i).getFzhsmcFive();
                arr[22]=listPzxq.get(i).getFzhsSix()==null ? "" : listPzxq.get(i).getFzhsSix();
                arr[23]=listPzxq.get(i).getFzhsmcSix()==null ? "" : listPzxq.get(i).getFzhsmcSix();
                arr[24]=listPzxq.get(i).getFzhsSeven()==null ? "" : listPzxq.get(i).getFzhsSeven();
                arr[25]=listPzxq.get(i).getFzhsmcSeven()==null ? "" : listPzxq.get(i).getFzhsmcSeven();
                arr[26]=listPzxq.get(i).getFzhsEight()==null ? "" : listPzxq.get(i).getFzhsEight();
                arr[27]=listPzxq.get(i).getFzhsmcEight()==null ? "" : listPzxq.get(i).getFzhsmcEight();
                arr[28]=listPzxq.get(i).getFzhsNine()==null ? "" : listPzxq.get(i).getFzhsNine();
                arr[29]=listPzxq.get(i).getFzhsmcNine()==null ? "" : listPzxq.get(i).getFzhsmcNine();
                arr[30]=listPzxq.get(i).getFzhsTen()==null ? "" : listPzxq.get(i).getFzhsTen();
                arr[31]=listPzxq.get(i).getFzhsmcTen()==null ? "" : listPzxq.get(i).getFzhsmcTen();
                arr[32]=listPzxq.get(i).getPjh()==null ? "" : listPzxq.get(i).getPjh();
                arr[33]=listPzxq.get(i).getPjrq()==null ? "" : listPzxq.get(i).getPjrq();
                datasetPzxq.add(arr);
            }

            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");

            //获取货赔表详情数据
            List<String[]> datasetHuopei = new ArrayList<>();
            for (int i = 0; i < listHuopei.size(); i++) {
                String[] arr = new String[19];
                arr[0] = listHuopei.get(i).getNum() == null ? "" : listHuopei.get(i).getNum();
                arr[1] = listHuopei.get(i).getShopCode() == null ? "" : listHuopei.get(i).getShopCode();
                arr[2] = listHuopei.get(i).getShopName() == null ? "" : listHuopei.get(i).getShopName();

                Date beginTime = listHuopei.get(i).getBeginTime();
                Date endTime = listHuopei.get(i).getEndTime();
                if (ToolUtil.isNotEmpty(beginTime) && ToolUtil.isNotEmpty(endTime)){
                    String begin = sdf1.format(beginTime);
                    String end = sdf1.format(endTime);
                    arr[3] = begin + " 至 " + end;
                }else {
                    arr[3] = "";
                }


                arr[4] = listHuopei.get(i).getClassName() == null ? "" : listHuopei.get(i).getClassName();
                arr[5] = listHuopei.get(i).getProCode() == null ? "" : listHuopei.get(i).getProCode();
                arr[6] = listHuopei.get(i).getProName() == null ? "" : listHuopei.get(i).getProName();
                arr[7] = listHuopei.get(i).getSpec() == null ? "" : listHuopei.get(i).getSpec();
                arr[8] = listHuopei.get(i).getUnit() == null ? "" : listHuopei.get(i).getUnit();
                arr[9] = listHuopei.get(i).getPrice() == null ? "" : listHuopei.get(i).getPrice();
                arr[10] = listHuopei.get(i).getThNum() == null ? "" : listHuopei.get(i).getThNum();
                arr[11] = listHuopei.get(i).getRealNum() == null ? "" : listHuopei.get(i).getRealNum();
                arr[12] = listHuopei.get(i).getNumTotal() == null ? "" : listHuopei.get(i).getNumTotal();
                arr[13] = listHuopei.get(i).getAmountTotal() == null ? "" : listHuopei.get(i).getAmountTotal();
                arr[14] = listHuopei.get(i).getSoup() == null ? "" : listHuopei.get(i).getSoup();
                arr[15] = listHuopei.get(i).getSoupAmount() == null ? "" : listHuopei.get(i).getSoupAmount().toString();
                arr[16] = listHuopei.get(i).getTurnover() == null ? "" : listHuopei.get(i).getTurnover().toString();
                arr[17] = listHuopei.get(i).getFive() == null ? "" : listHuopei.get(i).getFive().toString();
                arr[18] = listHuopei.get(i).getTen() == null ? "" : listHuopei.get(i).getTen().toString();
                datasetHuopei.add(arr);
            }

            //获取损益表详情数据
            List<String[]>datasetSunYi=new ArrayList<>();
            for (int i = 0; i < listSunYi.size(); i++) {
                String[] arr = new String[17];
                arr[0]=listSunYi.get(i).getNumber()==null ? "" : listSunYi.get(i).getNumber();
                arr[1]=listSunYi.get(i).getDm()==null ? "" : listSunYi.get(i).getDm();
                arr[2]=listSunYi.get(i).getTitle()==null ? "" : listSunYi.get(i).getTitle();
                arr[3]=listSunYi.get(i).getHang()==null ? "" : listSunYi.get(i).getHang();
                arr[4]=listSunYi.get(i).getJanuary()==null ? "" : listSunYi.get(i).getJanuary();
                arr[5]=listSunYi.get(i).getFebruary()==null ? "" : listSunYi.get(i).getFebruary();
                arr[6]=listSunYi.get(i).getMarch()==null ? "" : listSunYi.get(i).getMarch();
                arr[7]=listSunYi.get(i).getApril()==null ? "" : listSunYi.get(i).getApril();
                arr[8]=listSunYi.get(i).getMay()==null ? "" : listSunYi.get(i).getMay();
                arr[9]=listSunYi.get(i).getJune()==null ? "" : listSunYi.get(i).getJune();
                arr[10]=listSunYi.get(i).getJuly()==null ? "" : listSunYi.get(i).getJuly();
                arr[11]=listSunYi.get(i).getAugust()==null ? "" : listSunYi.get(i).getAugust();
                arr[12]=listSunYi.get(i).getSeptember()==null ? "" : listSunYi.get(i).getSeptember();
                arr[13]=listSunYi.get(i).getOctober()==null ? "" : listSunYi.get(i).getOctober();
                arr[14]=listSunYi.get(i).getNovember()==null ? "" : listSunYi.get(i).getNovember();
                arr[15]=listSunYi.get(i).getDecember()==null ? "" : listSunYi.get(i).getDecember();
                arr[16]=listSunYi.get(i).getTotal()==null ? "" : listSunYi.get(i).getTotal();
                datasetSunYi.add(arr);
            }

            //获取资产负债表详情数据
            List<String[]>datasetZiChan=new ArrayList<>();
            for (int i = 0; i < listZiChan.size(); i++) {
                String[] arr = new String[18];
                arr[0]=listZiChan.get(i).getNumber()==null ? "" : listZiChan.get(i).getNumber();
                arr[1]=listZiChan.get(i).getDm()==null ? "" : listZiChan.get(i).getDm();
                arr[2]=listZiChan.get(i).getTitle()==null ? "" : listZiChan.get(i).getTitle();
                arr[3]=listZiChan.get(i).getHang()==null ? "" : listZiChan.get(i).getHang();
                arr[4]=listZiChan.get(i).getYesterdayDecember()==null ? " " : listZiChan.get(i).getYesterdayDecember();
                arr[5]=listZiChan.get(i).getJanuary()==null ? " " : listZiChan.get(i).getJanuary();
                arr[6]=listZiChan.get(i).getFebruary()==null ? " " : listZiChan.get(i).getFebruary();
                arr[7]=listZiChan.get(i).getMarch()==null ? " " : listZiChan.get(i).getMarch();
                arr[8]=listZiChan.get(i).getApril()==null ? " " : listZiChan.get(i).getApril();
                arr[9]=listZiChan.get(i).getMay()==null ? " " : listZiChan.get(i).getMay();
                arr[10]=listZiChan.get(i).getJune()==null ? " " : listZiChan.get(i).getJune();
                arr[11]=listZiChan.get(i).getJuly()==null ? " " : listZiChan.get(i).getJuly();
                arr[12]=listZiChan.get(i).getAugust()==null ? " " : listZiChan.get(i).getAugust();
                arr[13]=listZiChan.get(i).getSeptember()==null ? " " : listZiChan.get(i).getSeptember();
                arr[14]=listZiChan.get(i).getOctober()==null ? " " : listZiChan.get(i).getOctober();
                arr[15]=listZiChan.get(i).getNovember()==null ? " " : listZiChan.get(i).getNovember();
                arr[16]=listZiChan.get(i).getDecember()==null ? " " : listZiChan.get(i).getDecember();
                arr[17]=listZiChan.get(i).getTotal()==null ? " " : listZiChan.get(i).getTotal();
                datasetZiChan.add(arr);
            }

            //费用明细表头
            String[] handers1 = {"序号","店名","日期","科目编码","科目名称","凭证字号","摘要","对方科目","借方","贷方","方向","余额"};
            //辅助核算余额表头
            String[] handers2 = {"序号","店名","科目类别","科目编码","科目名称","辅助核算","期初方向","期初余额","本期借方","本期贷方","借方累计","贷方累计","期末方向","期末余额"};
            //现金日记账表头
            String[] handersXjrjz = {"序号","店名","日期","科目编码","科目名称","凭证字号","摘要","对方科目","借方","贷方","方向","余额"};
            //银行营业额表头
            String[] handersYhyye = {"序号","店名","日期","科目编码","科目名称","凭证字号","摘要","对方科目","借方","贷方","方向","余额"};
            //应收明细表头
            String[] handersSyfs = {"序号","店名","日期","收银方式编码","收银方式名称","科目编码","科目名称","凭证字号","摘要","对方科目","借方","贷方","方向","余额"};
            //门店盘点表头
            String[] handersMdpd = {"店名","品项类别","品项编号","品项名称","包装信息","品项规格","账存数量1","单位1","账存数量2","单位2","实盘数量1","单位1","实盘数量2",
                    "单位2","账存金额","含税账存金额","差异数量","单位","盈亏金额","含税盘亏金额","辅助单位","辅助单位账存数量","辅助单位实盘数量","辅助单位差异数量","库位号","批次号","备注"};
            //仓库品项表头
            String[] handersCkpx = {"序号","门店编号","门店名称","品项类别名称","品项编号","品项名称","品项规格","单位","非税单价","期初数量","期初金额","累计入库数量",
                    "累计入库金额","累计店间调入数量","累计店间调入金额","累计出库数量","累计出库金额","累计店间调出数量","累计店间调出成本金额","累计盘盈数量","累计盘盈金额","累计盘亏数量",
                    "累计盘亏金额","本期结存数量","本期结存金额"};
            //银行流水表头
            String[] handersYhls = {"店名","日期","支出","收入","余额","摘要","户名","时间","ID"};
            //凭证详情表头
            String[] handersPzxq = {"店名","制单日期","凭证类别","凭证编号","附件张数","创建人","序号","科目编号","科目名称","摘要","借方金额","贷方金额","辅助核算1",
                    "辅助核算1名称","辅助核算2", "辅助核算2名称","辅助核算3", "辅助核算3名称","辅助核算4", "辅助核算4名称","辅助核算5", "辅助核算5名称","辅助核算6",
                    "辅助核算6名称","辅助核算7", "辅助核算7名称","辅助核算8", "辅助核算8名称","辅助核算9", "辅助核算9名称","辅助核算10", "辅助核算10名称",
                    "票据号","票据日期"};

            //货赔表表头
            String[] handersHuopei = {"", "门店编号", "门店名称", "业务日期", "品相类别名称", "品相编号", "品相名称", "规格", "单位", "非税单价", "理论耗用数量", "实际耗用数量",
                    "耗用数量分差", "耗用金额分差", "加汤等", "金额", "营业额", "营业额万分之五", "营业额万分之十"};

            //损益表表头
            String[] handersSunYi = {"财务码", "店名", "项 目", "行次", "1月份", "2月份", "3月份", "4月份", "5月份", "6月份", "7月份",
                    "8月份", "9月份", "10月份", "11月份", "12月份", "合计"};

            //资产负债表表头
            String[] handersZiChan = { "财务码","店名", "项 目", "行次","年初数", "1月份", "2月份", "3月份", "4月份", "5月份", "6月份", "7月份",
                    "8月份", "9月份", "10月份", "11月份", "12月份", "合计"};

            //对象
            ExcelExp fymx = new ExcelExp("费用明细表", handers1, dataset);
            ExcelExp fzhs = new ExcelExp("辅助核算余额表", handers2, datasetFzhs);
            ExcelExp xjrjz = new ExcelExp("现金日记账", handersXjrjz, datasetXjrjz);
            ExcelExp yhyye = new ExcelExp("银行营业额日记账", handersYhyye, datasetYhyye);
            ExcelExp ysmx = new ExcelExp("应收明细表", handersSyfs, datasetYhmx);
            ExcelExp mdpd = new ExcelExp("盘点表", handersMdpd, datasetMdpd);
            ExcelExp ckpx = new ExcelExp("收发存", handersCkpx, datasetCkpx);
            ExcelExp yhls = new ExcelExp("出纳银行明细", handersYhls, datasetYhls);
            ExcelExp pzxq = new ExcelExp("凭证详情", handersPzxq, datasetPzxq);
            ExcelExp hp = new ExcelExp("货赔表", handersHuopei, datasetHuopei);
            ExcelExp SunYi = new ExcelExp("损益表", handersSunYi, datasetSunYi);
            ExcelExp ZiChan = new ExcelExp("资产负债表", handersZiChan, datasetZiChan);

            List<ExcelExp> mysheet = new ArrayList<>();
            mysheet.add(fymx);
            mysheet.add(fzhs);
            mysheet.add(xjrjz);
            mysheet.add(yhyye);
            mysheet.add(ysmx);
            mysheet.add(mdpd);
            mysheet.add(ckpx);
            mysheet.add(yhls);
            mysheet.add(pzxq);
            mysheet.add(hp);
            mysheet.add(SunYi);
            mysheet.add(ZiChan);
            //生成sheet
            ExcelUtil.exportManySheetExcel(url, mysheet);
            //下载sheet
            ExcelUtil.getExcel(url, fileName, response);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private String getString(DecimalFormat format, String dflj2) {
        String dflj = dflj2 == null ? "0" : dflj2.replace(",","").replace("，","");
        dflj = format.format(new BigDecimal(dflj));
        if (dflj.equals("0.00")) {
            dflj = "";
        }
        return dflj;
    }
    /**
     * 导入仓库品项excel
     */
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public Boolean batchImportCkpx(String fileName, MultipartFile file,String dm) throws Exception {
        boolean notNull = false;
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        //excel中多个sheet表，获取所有sheet表的数量
        int sheets = wb.getNumberOfSheets();
        for (int i = 0; i < sheets; i++) {
            Sheet sheet = wb.getSheetAt(i);
            if (sheet != null) {
                notNull = true;
            }
            //获取业务日期
            Row rowss = sheet.getRow(0);
            String rows=rowss.getCell(0).toString().replaceAll("\\s*", "");
            String ywrq=rows.substring(rows.indexOf(":") + 1).substring(0,7);
            List<BtyyCwCkpx>list=baseMapper.selectList(new QueryWrapper<BtyyCwCkpx>().eq("mdmc",dm)
                    .eq("ywrq",ywrq));
            if(list.size()>0){
                baseMapper.delete(new QueryWrapper<BtyyCwCkpx>().eq("mdmc",dm)
                        .eq("ywrq",ywrq));
            }
            int lastRowNum = sheet.getLastRowNum();
            for (int r = 2; r <= lastRowNum; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                //获取单元格
                for(int m=1;m<25;m++){
                    Cell cell = row.getCell(m);
                    if (ToolUtil.isNotEmpty(cell)) {
                        cell.setCellType(CellType.STRING);
                    }
                }
                String mdbh = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
                //String mdmc = row.getCell(2) == null ? "" : row.getCell(2).getStringCellValue();
                String mdmc = dm;
                String pxlbmc = row.getCell(3) == null ? "" : row.getCell(3).getStringCellValue();
                String pxbh = row.getCell(4) == null ? "" : row.getCell(4).getStringCellValue();
                String pxmc = row.getCell(5) == null ? "" : row.getCell(5).getStringCellValue();
                String pxgg = row.getCell(6) == null ? "" : row.getCell(6).getStringCellValue();
                String dw = row.getCell(7) == null ? "" : row.getCell(7).getStringCellValue();
                String fsdj = row.getCell(8) == null ? "" : row.getCell(8).getStringCellValue();
                String qcsl = row.getCell(9) == null ? "" : row.getCell(9).getStringCellValue();
                String qcje = row.getCell(10) == null ? "" : row.getCell(10).getStringCellValue();
                String ljrksl = row.getCell(11) == null ? "" : row.getCell(11).getStringCellValue();
                String ljrkje = row.getCell(12) == null ? "" : row.getCell(12).getStringCellValue();
                String ljdrsl = row.getCell(13) == null ? "" : row.getCell(13).getStringCellValue();
                String ljdrje = row.getCell(14) == null ? "" : row.getCell(14).getStringCellValue();
                String ljcksl = row.getCell(15) == null ? "" : row.getCell(15).getStringCellValue();
                String ljckje = row.getCell(16) == null ? "" : row.getCell(16).getStringCellValue();
                String ljdcsl = row.getCell(17) == null ? "" : row.getCell(17).getStringCellValue();
                String ljdcje = row.getCell(18) == null ? "" : row.getCell(18).getStringCellValue();
                String ljpysl = row.getCell(19) == null ? "" : row.getCell(19).getStringCellValue();
                String ljpyje = row.getCell(20) == null ? "" : row.getCell(20).getStringCellValue();
                String ljpksl = row.getCell(21) == null ? "" : row.getCell(21).getStringCellValue();
                String ljpkje = row.getCell(22) == null ? "" : row.getCell(22).getStringCellValue();
                String bqjcsl = row.getCell(23) == null ? "" : row.getCell(23).getStringCellValue();
                String bqjcje = row.getCell(24) == null ? "" : row.getCell(24).getStringCellValue();
                BtyyCwCkpx ckpx = new BtyyCwCkpx();
                ckpx.setId(UUIDUtils.getId());
                ckpx.setYwrq(ywrq);
                ckpx.setMdbh(mdbh);
                ckpx.setMdmc(mdmc);
                ckpx.setPxlbmc(pxlbmc);
                ckpx.setPxbh(pxbh);
                ckpx.setPxmc(pxmc);
                ckpx.setPxgg(pxgg);
                ckpx.setDw(dw);
                ckpx.setFsdj(fsdj);
                ckpx.setQcsl(qcsl);
                ckpx.setQcje(qcje);
                ckpx.setLjrksl(ljrksl);
                ckpx.setLjrkje(ljrkje);
                ckpx.setLjdrsl(ljdrsl);
                ckpx.setLjdrje(ljdrje);
                ckpx.setLjcksl(ljcksl);
                ckpx.setLjckje(ljckje);
                ckpx.setLjdcsl(ljdcsl);
                ckpx.setLjdcje(ljdcje);
                ckpx.setLjpysl(ljpysl);
                ckpx.setLjpyje(ljpyje);
                ckpx.setLjpksl(ljpksl);
                ckpx.setLjpkje(ljpkje);
                ckpx.setBqjcsl(bqjcsl);
                ckpx.setBqjcje(bqjcje);
                ckpxMapper.insert(ckpx);
            }
        }
        return notNull;
    }

}
